package com.credithc.customers.base.mapper;

import com.credithc.baseserv.core.mapper.BsBaseMapper;
import com.credithc.customers.base.po.AccountInfoStatisticPo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @Title:
 * @Author: fby
 * @Date: 2019/5/6 11:00
 * @Description: com.credithc.customers.base.mapper
 * @Version: V1.0.0
 */
@Mapper
@Repository
public interface AccountInfoStatisticMapper extends BsBaseMapper<AccountInfoStatisticPo> {

    @Select("SELECT account_type AS accountType,account.sub_system_sign AS subSystemSign,account_status AS accountStatus,#{yesterDay} AS date,count(1) AS sum,CURRENT_TIMESTAMP () AS createTime FROM cust_account_info account WHERE account.account_create_time < #{date} GROUP BY account_type,account.sub_system_sign,account_status"
    )
    List<AccountInfoStatisticPo> getAccountInit(@Param("date") String date, @Param("yesterDay") String yesterDay);

    @Select({"<script>",
            "SELECT SUM(sum) FROM",
            "account_info_statistic",
            "WHERE date = #{date}",
            "<when test='accountStatus!=null'>",
            "AND account_status = #{accountStatus}",
            "</when>",
            "<when test='subSystemSign!=null'>",
            "AND sub_system_sign = #{subSystemSign}",
            "</when>",
            "<when test='accountType!=null'>",
            "AND account_type = #{accountType}",
            "</when>",
            "</script>"})
    String getPastSum(@Param("subSystemSign") String subSystemSign, @Param("accountType") String accountType, @Param("accountStatus") Integer accountStatus,@Param("date") String date);

    @Select({"<script>",
            "SELECT count(*) FROM ",
            "cust_account_info account",
            "WHERE account.account_create_time BETWEEN #{endDate} AND #{nextDate}",
            "<when test='accountStatus!=null'>",
            "AND account.account_status = #{accountStatus}",
            "</when>",
            "<when test='subSystemSign!=null'>",
            "AND account.sub_system_sign = #{subSystemSign}",
            "</when>",
            "<when test='accountType!=null'>",
            "AND account.account_type = #{accountType}",
            "</when>",
            "</script>"})
    int getTodaySumByAll(@Param("subSystemSign") String subSystemSign, @Param("accountType") String accountType, @Param("accountStatus") Integer accountStatus, @Param("nextDate") String nextDate, @Param("endDate") String endDate);

    @Select("SELECT account_type AS accountType,account.sub_system_sign AS subSystemSign,account_status AS accountStatus,'0000-00-00' AS date,count(1) AS sum,CURRENT_TIMESTAMP () AS createTime FROM cust_account_info account LEFT JOIN cust_id_info id ON account.customer_no = id.customer_no WHERE account.account_create_time is NULL GROUP BY account_type,account.sub_system_sign,account_status"
    )
    List<AccountInfoStatisticPo> getProductInitTimeNull();

    @Select("SELECT account_type AS accountType,account.sub_system_sign AS subSystemSign,account_status AS accountStatus,#{yesterDay} AS date,count(1) AS sum,CURRENT_TIMESTAMP () AS createTime FROM cust_account_info account WHERE account.account_create_time < #{date} AND account.sub_system_sign = #{subSystemSign} GROUP BY account_type,account.sub_system_sign,account_status"
    )
    List<AccountInfoStatisticPo> getAccountInitSys(@Param("date") String date, @Param("yesterDay") String yesterDay,@Param("subSystemSign") String subSystemSign);
}
